package com.unieagle.exam.db;

import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.unieagle.exam.model.Answer;
import com.unieagle.exam.model.Question;
import com.unieagle.exam.model.Subject;

public class ExamDBHelper extends SQLiteOpenHelper { 
	
	private final static String DATABASE_NAME = "exam.db"; 
	
	private final static int DATABASE_VERSION = 1; 
	
	private final static String SUBJECT_TABLE_NAME = "exam_subject";
	
	private final static String QUESTION_TABLE_NAME = "exam_question";
	
	private final static String ANSWER_TABLE_NAME = "exam_answer";
	
	private final static String EXAM_RESULT_TABLE_NAME = "exam_result";
	
	public final static String SUBJECT_ID = "subject_id";
	
	public final static String EXAM_RESULT_ID = "exam_result_id"; 
	
	public final static String SUBJECT_PARENT_ID = "subject_p_id"; 
	
	public final static String SUBJECT_TITLE = "subject_title";
	
	public final static String QUESTION_ID = "question_id"; 
	
	public final static String QUESTION_SUBJECT_ID = "question_subject_id"; 
	
	public final static String QUESTION_CONTENT = "question_content";
	
	public final static String QUESTION_TYPE = "question_type";
	
	public final static String QUESTION_CASE_ID = "question_case_id";
	
	public final static String QUESTION_IS_REQUIRED = "question_is_required";
	
	public final static String ANSWER_ID = "answer_id"; 
	
	public final static String ANSWER_CONTENT = "answer_content";
	
	public final static String ANSWER_CORRECT = "answer_correct";
	
	public final static String SCORE = "score";
 
	public ExamDBHelper(Context context) { 
		super(context, DATABASE_NAME, null, DATABASE_VERSION); 
	} 
	
	@Override
	public void onCreate(SQLiteDatabase db) { 
		String sql = "CREATE TABLE " + SUBJECT_TABLE_NAME + " (" + SUBJECT_ID 
		+ " INTEGER primary key, " + SUBJECT_PARENT_ID + " INTEGER, "+ SUBJECT_TITLE +" text);"; 
		db.execSQL(sql); 
		sql = "CREATE TABLE " + QUESTION_TABLE_NAME + " (" + QUESTION_ID 
				+ " INTEGER primary key, " + QUESTION_SUBJECT_ID + " INTEGER, "+ QUESTION_CONTENT +" text, " +
				QUESTION_TYPE + " text, " +  QUESTION_CASE_ID + " INTEGER, " + QUESTION_IS_REQUIRED + " INTEGER);"; 
		db.execSQL(sql);
		sql = "CREATE TABLE " + ANSWER_TABLE_NAME + " (" + ANSWER_ID 
				+ " INTEGER primary key, " + QUESTION_ID + " INTEGER, "+ ANSWER_CONTENT +" text, " + ANSWER_CORRECT + "  INTEGER);"; 
		db.execSQL(sql);
		sql = "CREATE TABLE " + EXAM_RESULT_TABLE_NAME + " (" + EXAM_RESULT_ID 
				+ " INTEGER primary key autoincrement, " + QUESTION_CASE_ID + " INTEGER, " + SCORE + " INTEGER);"; 
		db.execSQL(sql); 
	} 
	
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { 
		String sql = "DROP TABLE IF EXISTS " + SUBJECT_TABLE_NAME; 
		db.execSQL(sql); 
		sql = "DROP TABLE IF EXISTS " + QUESTION_TABLE_NAME; 
		db.execSQL(sql);
		sql = "DROP TABLE IF EXISTS " + ANSWER_TABLE_NAME; 
		db.execSQL(sql);
		onCreate(db); 
	} 
	 
	public List<Subject> getSubject1() {  //获取工种
		List<Subject> list = new ArrayList<Subject>();
		SQLiteDatabase db = this.getReadableDatabase(); 
		Cursor cursor = db .query(SUBJECT_TABLE_NAME, null, SUBJECT_PARENT_ID + "=?", new String[]{String.valueOf(0)},
				null, null, null);
		while(cursor.moveToNext()) {
			Subject s = new Subject();
			s.setId(cursor.getInt(0));
			s.setParentId(cursor.getInt(1));
			s.setTitle(cursor.getString(2));
			list.add(s);
		}
		cursor.close();
		db.close();
		return list;
	}
	
	public List<Subject> getSubject2(int id) {  //获取工种下的二级科目
		List<Subject> list = new ArrayList<Subject>();
		SQLiteDatabase db = this.getReadableDatabase(); 
		Cursor cursor = db .query(SUBJECT_TABLE_NAME, null, SUBJECT_PARENT_ID + "=?", new String[]{String.valueOf(id)},
				null, null, null);
		while(cursor.moveToNext()) {
			Subject s = new Subject();
			s.setId(cursor.getInt(0));
			s.setParentId(cursor.getInt(1));
			s.setTitle(cursor.getString(2));
			list.add(s);
		}
		cursor.close();
		db.close();
		return list;
	}
	
	public List<Question> getRandomCaseQuestionsBySubject(int subjectId) {
		List<Integer> caseIdList = new ArrayList<Integer>();
		List<Question> list = new ArrayList<Question>();
		int caseId = 0;
		SQLiteDatabase db = this.getReadableDatabase(); 
		Cursor cursor = db.query(true, QUESTION_TABLE_NAME, new String[]{QUESTION_CASE_ID}, QUESTION_SUBJECT_ID + "=?",
				new String[]{String.valueOf(subjectId)}, 
				null, null, null, null);
		while(cursor.moveToNext()) {
			caseIdList.add(cursor.getInt(0));
		}
		Random random = new Random();
		int random_index = random.nextInt(caseIdList.size());
		caseId = caseIdList.get(random_index);
		cursor.close();
		
		cursor = db.query(QUESTION_TABLE_NAME, null, QUESTION_CASE_ID + "=? AND " + QUESTION_SUBJECT_ID + "=?",
				new String[]{String.valueOf(caseId), String.valueOf(subjectId)}, 
				null, null, null);
		while(cursor.moveToNext()) {
			Question q = new Question();
			q.setId(cursor.getInt(0));
			q.setSubjectId(cursor.getInt(1));
			q.setContent(cursor.getString(2));
			q.setType(cursor.getString(3));
			q.setCaseId(cursor.getLong(4));
			q.setRequired(cursor.getInt(5));
			if(q.getType().equals("judge")) {
				q.setTypeName("判断题");
			} else if(q.getType().equals("single_choice")) {
				q.setTypeName("单选题");
			} else if(q.getType().equals("multiple_choice")) {
				q.setTypeName("多选题");
			}
			list.add(q);
		}
		cursor.close();
		db.close();
		return list;
	}
	
	public List<Integer> getAllCasesBySubject(int subjectId) {
		List<Integer> caseIdList = new ArrayList<Integer>();
		SQLiteDatabase db = this.getReadableDatabase(); 
		Cursor cursor = db.query(true, QUESTION_TABLE_NAME, new String[]{QUESTION_CASE_ID}, QUESTION_SUBJECT_ID + "=?",
				new String[]{String.valueOf(subjectId)}, 
				null, null, null, null);
		while(cursor.moveToNext()) {
			caseIdList.add(cursor.getInt(0));
		}
		db.close();
		return caseIdList;
	}
	
	public List<Question> getQuestionsBySubjectAndCase(int caseId, int subjectId) {
		List<Question> list = new ArrayList<Question>();
		SQLiteDatabase db = this.getReadableDatabase(); 
		Cursor cursor = db.query(QUESTION_TABLE_NAME, null, QUESTION_CASE_ID + "=? AND " + QUESTION_SUBJECT_ID + "=?",
				new String[]{String.valueOf(caseId), String.valueOf(subjectId)}, 
				null, null, null);
		while(cursor.moveToNext()) {
			Question q = new Question();
			q.setId(cursor.getInt(0));
			q.setSubjectId(cursor.getInt(1));
			q.setContent(cursor.getString(2));
			q.setType(cursor.getString(3));
			q.setCaseId(cursor.getLong(4));
			q.setRequired(cursor.getInt(5));
			if(q.getType().equals("judge")) {
				q.setTypeName("判断题");
			} else if(q.getType().equals("single_choice")) {
				q.setTypeName("单选题");
			} else if(q.getType().equals("multiple_choice")) {
				q.setTypeName("多选题");
			}
			list.add(q);
		}
		cursor.close();
		db.close();
		return list;
	}
	
	public List<Answer> getAnswer(int qId) {
		List<Answer> list = new ArrayList<Answer>();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(ANSWER_TABLE_NAME, null, QUESTION_ID + "=?", 
				new String[]{String.valueOf(qId)}, null, null, null);
		while(cursor.moveToNext()) {
			Answer ans = new Answer();
			ans.setId(cursor.getInt(0));
			ans.setQuestionId(cursor.getInt(1));
			ans.setContent(cursor.getString(2));
			ans.setCorrect(cursor.getInt(3));
			list.add(ans);
		}
		cursor.close();
		db.close();
		return list;
	}
	
	public Subject getSubjectById(long id) {
		Subject subject = new Subject();
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.query(SUBJECT_TABLE_NAME, null, SUBJECT_ID + "=?", 
				new String[]{String.valueOf(id)}, null, null, null);
		while(cursor.moveToNext()) {
			subject.setId(cursor.getInt(0));
			subject.setParentId(cursor.getInt(1));
			subject.setTitle(cursor.getString(2));
		}
		cursor.close();
		db.close();
		return subject;
	}
	
	public boolean updateSubjects(List<Subject> list) {
		SQLiteDatabase db = this.getWritableDatabase(); 
		db.beginTransaction(); 
		for(Subject s : list) {
			if(s.getUpdateStatus().equals("new")) {
				insertSubject(db, s);
			} else if(s.getUpdateStatus().equals("updated")) {
				updateSubject(db, s);
			} else {
				deleteSubject(db, s);
			}
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		db.close();
		return true;
	}
	
	private long insertSubject(SQLiteDatabase db, Subject s) 
	{ 
		ContentValues cv = new ContentValues(); 
		cv.put(SUBJECT_ID, s.getId()); 
		cv.put(SUBJECT_PARENT_ID, s.getParentId());
		cv.put(SUBJECT_TITLE, s.getTitle());
		long row = db.insert(SUBJECT_TABLE_NAME, null, cv);
		return row; 
	}	
	
	private void deleteSubject(SQLiteDatabase db, Subject s) {
		db.delete(SUBJECT_TABLE_NAME, "subject_id=?", new String []{String.valueOf(s.getId())});
	}
	
	private void updateSubject(SQLiteDatabase db, Subject s) {
		ContentValues values = new ContentValues();
	    values.put(SUBJECT_PARENT_ID, s.getParentId());
	    values.put(SUBJECT_TITLE, s.getTitle());
	    db.update(SUBJECT_TABLE_NAME, values, "subject_id=?",  new String []{String.valueOf(s.getId())});
	}
	
	public boolean updateQuestionsAndAnswers(List<Question> list) {
		SQLiteDatabase db = this.getWritableDatabase(); 
		db.beginTransaction(); 
		for(Question q : list) {
			if(q.getUpdateStatus().equals("new")) {
				insertQuestion(db, q);
			} else if(q.getUpdateStatus().equals("updated")) {
				updateQuestion(db, q);
			} else {
				deleteQuestion(db, q);
			}
		}
		db.setTransactionSuccessful();
		db.endTransaction();
		db.close();
		return true;
	}
	
	private void insertQuestion(SQLiteDatabase db, Question q) {
		ContentValues cv_q = new ContentValues();
		ContentValues cv_a = new ContentValues(); 
		cv_q = new ContentValues(); 
		cv_q.put(QUESTION_ID, q.getId()); 
		cv_q.put(QUESTION_SUBJECT_ID, q.getSubjectId());
		cv_q.put(QUESTION_CONTENT, q.getContent());
		cv_q.put(QUESTION_TYPE, q.getType());
		cv_q.put(QUESTION_CASE_ID, q.getCaseId());
		cv_q.put(QUESTION_IS_REQUIRED, q.getRequired());
		db.insert(QUESTION_TABLE_NAME, null, cv_q);
		for(Answer ans : q.getAnswer()) {
			cv_a = new ContentValues(); 
			cv_a.put(ANSWER_ID, ans.getId()); 
			cv_a.put(QUESTION_ID, ans.getQuestionId());
			cv_a.put(ANSWER_CONTENT, ans.getContent());
			cv_a.put(ANSWER_CORRECT, ans.getCorrect());
			db.insert(ANSWER_TABLE_NAME, null, cv_a);
		}
	}
	
	private void deleteQuestion(SQLiteDatabase db, Question q) {
		db.delete(QUESTION_TABLE_NAME, "question_id=?", new String []{String.valueOf(q.getId())});
		for(Answer ans : q.getAnswer()) {
			deleteAnswer(db, ans);
		}
	}
	
	private void updateQuestion(SQLiteDatabase db, Question q) {
		ContentValues cv_q = new ContentValues();
		cv_q = new ContentValues(); 
		cv_q.put(QUESTION_ID, q.getId()); 
		cv_q.put(QUESTION_SUBJECT_ID, q.getSubjectId());
		cv_q.put(QUESTION_CONTENT, q.getContent());
		cv_q.put(QUESTION_TYPE, q.getType());
		cv_q.put(QUESTION_CASE_ID, q.getCaseId());
		cv_q.put(QUESTION_IS_REQUIRED, q.getRequired());
	    db.update(QUESTION_TABLE_NAME, cv_q, "question_id=?",  new String []{String.valueOf(q.getId())});
	    for(Answer ans : q.getAnswer()) {
			updateAnswer(db, ans);
		}
	}
	
	private void deleteAnswer(SQLiteDatabase db, Answer ans) {
		db.delete(ANSWER_TABLE_NAME, "answer_id=?", new String []{String.valueOf(ans.getId())});
	}
	
	private void updateAnswer(SQLiteDatabase db, Answer ans) {
		ContentValues cv_a = new ContentValues(); 
		cv_a.put(ANSWER_ID, ans.getId()); 
		cv_a.put(QUESTION_ID, ans.getQuestionId());
		cv_a.put(ANSWER_CONTENT, ans.getContent());
		cv_a.put(ANSWER_CORRECT, ans.getCorrect());
	    db.update(ANSWER_TABLE_NAME, cv_a, "answer_id=?",  new String []{String.valueOf(ans.getId())});
	}
	
	public void insertExamResult(int case_id, int score) {
		SQLiteDatabase db = this.getWritableDatabase(); 
		ContentValues cv = new ContentValues();
		cv.put(QUESTION_CASE_ID, case_id);
		cv.put(SCORE, score);
		db.insert(EXAM_RESULT_TABLE_NAME, null, cv);
		db.close();
	}
	
	public List<Integer> getExamResultByCaseId(long case_id) {
		List<Integer> resultList = new ArrayList<Integer>();
		SQLiteDatabase db = this.getReadableDatabase(); 
		Cursor cursor = db.query(EXAM_RESULT_TABLE_NAME, null, QUESTION_CASE_ID + "=?",
				new String[]{String.valueOf(case_id)}, 
				null, null, null, null);
		while(cursor.moveToNext()) {
			resultList.add(cursor.getInt(2));
		}
		cursor.close();
		db.close();
		return resultList;
	}
	
	/**
	 * 清空数据库，方便调试update题库接口
	 */
	public void clearDataBase() {
		SQLiteDatabase db = this.getReadableDatabase(); 
		db.execSQL("DELETE FROM exam_subject");  
		db.execSQL("DELETE FROM exam_question");  
		db.execSQL("DELETE FROM exam_answer");  
	}
	
}